﻿using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;

namespace PeiYue.Common.Helper
{
    public class ExcelHelper
    {
        /// <summary>
        /// 5万条数据以内导出
        /// </summary>
        /// <param name="sourceTable">数据源</param>
        /// <param name="header">显示标题</param>
        /// <returns></returns>
        public static byte[] ExportDataTableToExcel(FileInfo template, DataTable sourceTable, bool header)
        {
            if (sourceTable == null)
                return null;

            ////超过5万条使用csv格式
            //if (sourceTable.Rows.Count > 50000)
            //{
            //    return ToCsv(sourceTable);
            //}
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            ExcelPackage excel = new ExcelPackage(template);
            ExcelWorksheet sheet = excel.Workbook.Worksheets.ElementAtOrDefault(0);// excel.Workbook.Worksheets.Copy("Sheet1", "sheet99");// .Add("sheet99");
            //excel.Workbook.Worksheets.Copy("Sheet1", "sheet99");
            var ii = 0;

            if (header)
            {
                ii++;
                for (int i = 1; i <= sourceTable.Columns.Count; i++)
                {
                    sheet.Cells[ii, i].Value = sourceTable.Columns[i - 1];
                    //sourceTable.AsEnumerable().Max(d=> d.Field<string>("UNCHIN_CD"))
                }
            }
            foreach (DataRow row in sourceTable.Rows)
            {
                ii++;
                for (int i = 1; i <= sourceTable.Columns.Count; i++)
                {
                    sheet.Cells[ii, i].Value = row[sourceTable.Columns[i - 1]].ToString();
                }
            }

            MemoryStream ms = new MemoryStream();
            excel.SaveAs(ms);
            var result = ms.ToArray();

            ms.Close();
            ms.Dispose();
            return result;
        }


        /// <summary>
        /// 导出DataSet
        /// </summary>
        /// <param name="sourceTable">数据源</param>
        /// <param name="header">显示标题</param>
        /// <returns></returns>
        public static byte[] ExportDataSetToExcel(FileInfo template, DataSet data, bool header)
        {
            if (data == null)
                return null;
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            ExcelPackage excel = new ExcelPackage(template);

            ExcelWorksheet sheet = excel.Workbook.Worksheets.ElementAtOrDefault(0);
            for (int j = 0; j < data.Tables.Count; j++)
            {
                if (j > 0)
                {
                    excel.Workbook.Worksheets.Add("sheet" + j);
                    sheet = excel.Workbook.Worksheets.ElementAtOrDefault(j);
                }
                var ii = 0;
                var dt = data.Tables[j];
                if (header)
                {
                    ii++;
                    for (int i = 1; i <= dt.Columns.Count; i++)
                    {
                        sheet.Cells[ii, i].Value = dt.Columns[i - 1];
                        //sourceTable.AsEnumerable().Max(d=> d.Field<string>("UNCHIN_CD"))
                    }
                }
                foreach (DataRow row in dt.Rows)
                {
                    ii++;
                    for (int i = 1; i <= dt.Columns.Count; i++)
                    {
                        sheet.Cells[ii, i].Value = row[dt.Columns[i - 1]].ToString();
                    }
                }

            }

            MemoryStream ms = new MemoryStream();
            excel.SaveAs(ms);
            var result = ms.ToArray();

            ms.Close();
            ms.Dispose();
            return result;
        }


        ///// <summary>
        ///// 导出Csv格式文件
        ///// </summary>
        ///// <param name="dtData"></param>
        ///// <returns></returns>
        //public static byte[] ToCsv(DataTable dtData)
        //{
        //    System.Web.HttpContext context = System.Web.HttpContext.Current;
        //    StringBuilder sb = new StringBuilder();
        //    //列名
        //    int count = Convert.ToInt32(dtData.Columns.Count);
        //    for (int i = 0; i < count; i++)
        //    {
        //        sb.Append(dtData.Columns[i].ColumnName + ",");
        //    }
        //    sb.Append("\n");
        //    //行数据
        //    for (int i = 0; i < dtData.Rows.Count; i++)
        //    {
        //        for (int j = 0; j < count; j++)
        //        {
        //            string strName = dtData.Rows[i][j].AsString().Replace("\n", "").Replace(",", "_x002C");
        //            sb.Append(strName + "\t,");
        //        }
        //        sb.Append("\n");
        //    }
        //    StringWriter sw = new StringWriter(sb);
        //    byte[] fileContents = Encoding.Default.GetBytes(sw.ToString());
        //    sw.Close();
        //    sw.Dispose();
        //    return fileContents;
        //}
    }
}
